﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlServerCe;
using System.Data;
using System.Windows.Forms;

namespace QuanLySieuThi_08520114_08520220
{
    class controller_bangluong
    {
        private SqlCeDataAdapter temp_adapter;
        private DataTable temp_table;
        private SqlCeConnection con = null;


        /*===================  Bảng Tham Số ====================*/

        public DataTable get_thamso()
        {
            try
            {
                bool check = ToanCuc.OpenDB(ref con);
                if (check == true)
                {
                    string sql = "";

                    sql = "Select * From THAMSO";
                    temp_adapter = new SqlCeDataAdapter(sql, con);
                    temp_table = new DataTable();
                    temp_adapter.Fill(temp_table);
                    ToanCuc.disconnect(ref con);
                    return temp_table;

                }
            }
            catch (SqlCeException ex)
            {
                ToanCuc.DisplayError(ex);
                return null;
            }
            catch (SystemException exx)
            {
                MessageBox.Show(exx.Message);
                return null;
            }
            return null;
        }

        public bool update_thamso(string phat_nghi, string phat_tre,string phat_khientrach)
        {
            try
            {
                bool check = ToanCuc.OpenDB(ref con);
                if (check == true)
                {
                    string sql = "";
                    sql = "Update THAMSO Set PhatNghi = '" + phat_nghi + "', PhatDiTre = '" + phat_tre + "',PhatKhienTrach = '"+phat_khientrach+"'";
                    SqlCeCommand cmd = new SqlCeCommand(sql, con);
                    cmd.ExecuteNonQuery();
                    ToanCuc.disconnect(ref con);
                    return true;
                }
            }
            catch (SqlCeException ex)
            {
                ToanCuc.DisplayError(ex);
                return false;
            }
            catch (SystemException exx)
            {
                MessageBox.Show(exx.Message);
                return false;
            }

            return false;
        }
         

        /*=====================================================*/


        /*===================  Bảng Lương ====================*/

        public DataTable get_bangluong(string thang_nam)
        {
            try
            {
                bool check = ToanCuc.OpenDB(ref con);
                if (check == true)
                {
                    string sql = "";

                    sql = "Select b.*,n.MaNhanVien,n.HoTen From BANGLUONG b LEFT JOIN NHANVIEN n on n.NhanVienID = b.NhanVienID where ThangNam ='" + thang_nam + "'";
                    temp_adapter = new SqlCeDataAdapter(sql, con);
                    temp_table = new DataTable();
                    temp_adapter.Fill(temp_table);
                    ToanCuc.disconnect(ref con);

                    if (temp_table.Rows.Count == 0)
                        return null;
                    
                    return temp_table;

                }
            }
            catch (SqlCeException ex)
            {
                ToanCuc.DisplayError(ex);
                return null;
            }
            catch (SystemException exx)
            {
                MessageBox.Show(exx.Message);
                return null;
            }
            return null;
        }

        public bool check_bangluong(string thang_nam)
        {
            try
            {
                bool check = ToanCuc.OpenDB(ref con);
                if (check == true)
                {
                    string sql = "";

                    sql = "Select Count(*) from BANGLUONG where ThangNam ='" + thang_nam + "'";
                    SqlCeCommand cmd = new SqlCeCommand(sql, con);
                    int count = (int)cmd.ExecuteScalar();

                    if (count > 0)
                    {
                        return false;
                    }

                    ToanCuc.disconnect(ref con);
                    return true;

                }
            }
            catch (SqlCeException ex)
            {
                ToanCuc.DisplayError(ex);
                return false;
            }
            catch (SystemException exx)
            {
                MessageBox.Show(exx.Message);
                return false;
            }
            return false;
        }

        public string get_date()
        {
            /*
             * ham lay thoi gian tinh luong gan nhat trong bang luong
             */
            try
            {
                bool check = ToanCuc.OpenDB(ref con);
                if (check == true)
                {
                    string sql = "";

                    sql = "Select MAX(ThangNam) as max From BANGLUONG";
                    temp_adapter = new SqlCeDataAdapter(sql, con);
                    temp_table = new DataTable();
                    temp_adapter.Fill(temp_table);
                    ToanCuc.disconnect(ref con);

                    if (temp_table.Rows.Count == 0)
                        return "";

                    return temp_table.Rows[0]["max"].ToString();

                }
            }
            catch (SqlCeException ex)
            {
                ToanCuc.DisplayError(ex);
                return "";
            }
            catch (SystemException exx)
            {
                MessageBox.Show(exx.Message);
                return "";
            }
            return "";

        }

        public bool make_bangluong(string thang_nam)
        {
            DataTable thamso = new DataTable();
            DataTable nhanvien = new DataTable();
            DataTable date = new DataTable();
            try
            {
                bool check = ToanCuc.OpenDB(ref con);
                if (check == true)
                {
                    string sql = "";

                    sql = "Select * From THAMSO";
                    temp_adapter = new SqlCeDataAdapter(sql, con);
                    temp_adapter.Fill(thamso);
                    temp_adapter.SelectCommand.CommandText = "Select * From NHANVIEN";
                    temp_adapter.SelectCommand.ExecuteNonQuery();
                    temp_adapter.Fill(nhanvien);
                    temp_adapter.SelectCommand.CommandText = "Select MAX(ThangNam) as max From BANGLUONG";
                    temp_adapter.SelectCommand.ExecuteNonQuery();
                    temp_adapter.Fill(date);

                    int tp_n, tp_t, tp_kt , luong;
                    DataRow row_ts = thamso.Rows[0];
                    SqlCeCommand cmd = new SqlCeCommand(sql,con);
                    string luong_temp = date.Rows[0]["max"].ToString();
                    int thang,nam;
                    foreach (DataRow row in nhanvien.Rows)
                    {
                        tp_n = Convert.ToInt32(row["NghiKhongPhep"]) * Convert.ToInt32(row_ts["PhatNghi"]);
                        tp_t = Convert.ToInt32(row["SoNgayDiTre"]) * Convert.ToInt32(row_ts["PhatDiTre"]);
                        tp_kt = Convert.ToInt32(row["SoLanBiKhienTrach"]) * Convert.ToInt32(row_ts["PhatKhienTrach"]);

                        thang = 1;
                        if (luong_temp != "")
                        {
                            thang = Convert.ToInt32(thang_nam.Substring(0, 2)) - Convert.ToInt32(luong_temp.Substring(0, 2));
                            nam = Convert.ToInt32(thang_nam.Substring(2, 4)) - Convert.ToInt32(luong_temp.Substring(2, 4));
                            thang = thang + nam * 12;
                            if (thang == 0)
                                thang = 1;
                        }

                        luong = Convert.ToInt32(row["MucLuongHienTai"]) * thang; 
                        luong = luong - tp_n - tp_t - tp_kt;
                        if (luong < 0)
                            luong = 0;

                        cmd.CommandText = "Insert into BANGLUONG (NhanVienID,ThangNam,MucLuong,TongTienLuong,TienPhatNghi,TienPhatDiTre,TienPhatKhienTrach,SoNgayDiLam,NghiKhongPhep,SoNgayDiTre,SoLanBiKhienTrach) values ('" + row["NhanVienID"].ToString() + "','" + thang_nam + "','" + row["MucLuongHienTai"].ToString() + "','" + luong.ToString() + "','" + tp_n + "','" + tp_t + "','" + tp_kt + "','" + row["SoNgayDiLam"].ToString() + "','" + row["NghiKhongPhep"].ToString() + "','" + row["SoNgayDiTre"].ToString() + "','" + row["SoLanBiKhienTrach"].ToString() + "')";
                        cmd.ExecuteNonQuery();

                        cmd.CommandText = "Update NHANVIEN set SoNgayDiLam = '0',NghiKhongPhep ='0',SoNgayDiTre = '0',SoLanBiKhienTrach = '0' where NhanVienID = '" + row["NhanVienID"] + "'";
                        cmd.ExecuteNonQuery();
                    }

                    ToanCuc.disconnect(ref con);

                    return true;

                }
            }
            catch (SqlCeException ex)
            {
                ToanCuc.DisplayError(ex);
                return false;
            }
            catch (SystemException exx)
            {
                MessageBox.Show(exx.Message);
                return false;
            }
            return false;
        }

        public bool del_bangluong(string thang_nam)
        {
            try
            {
                bool check = ToanCuc.OpenDB(ref con);
                if (check == true)
                {
                    string sql = "";
                    sql = "Delete From BANGLUONG Where ThangNam = '" + thang_nam + "'";
                    SqlCeCommand cmd = new SqlCeCommand(sql, con);
                    cmd.ExecuteNonQuery();
                    ToanCuc.disconnect(ref con);
                    return true;
                }
            }
            catch (SqlCeException ex)
            {
                ToanCuc.DisplayError(ex);
                return false;
            }
            catch (SystemException exx)
            {
                MessageBox.Show(exx.Message);
                return false;
            }

            return false;
        }

        /*=======================================================================*/

    }
}
